package dbTest

import java.sql.Connection
import java.sql.Statement

/**
 * MySQL基准测试实现
 * 192.168.180.15   123456
 *
 */
class MySQLBenchmark(
    jdbcUrl: String = "jdbc:mysql://127.0.0.1:3306/testdb",
    username: String = "root",
    password: String = "123qweQAZ"
) : DatabaseBenchmark("MySQL", jdbcUrl, username, password) {

    override fun createTable() {
        val connection = getConnection()
        connection.use {
            val statement = it.createStatement()
            statement.use {
                // 删除已存在的表
                try {
                    it.execute("DROP TABLE IF EXISTS test_table")
                } catch (e: Exception) {
                    // 表可能不存在，忽略错误
                }
                
                // 创建新表
                it.execute("""
                    CREATE TABLE test_table (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        email VARCHAR(255) NOT NULL,
                        age INT NOT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                """.trimIndent())
            }
        }
    }

    override fun insertData() {
        val connection = getConnection()
        connection.use {
            val preparedStatement = it.prepareStatement(
                "INSERT INTO test_table (name, email, age) VALUES (?, ?, ?)",
                Statement.RETURN_GENERATED_KEYS
            )
            preparedStatement.use {
                val name = "User${System.currentTimeMillis()}"
                val email = "user${System.currentTimeMillis()}@example.com"
                val age = (18..80).random()
                
                it.setString(1, name)
                it.setString(2, email)
                it.setInt(3, age)
                it.executeUpdate()
            }
        }
    }

    override fun selectData() {
        val connection = getConnection()
        connection.use {
            val preparedStatement = it.prepareStatement("SELECT * FROM test_table ORDER BY RAND() LIMIT 1")
            preparedStatement.use {
                val resultSet = it.executeQuery()
                resultSet.use {
                    if (it.next()) {
                        // 简单读取数据
                        val id = it.getInt("id")
                        val name = it.getString("name")
                        val email = it.getString("email")
                        val age = it.getInt("age")
                    }
                }
            }
        }
    }

    override fun updateData() {
        val connection = getConnection()
        connection.use {
            val preparedStatement = it.prepareStatement(
                "UPDATE test_table SET name = ?, email = ?, age = ? WHERE id = (SELECT id FROM (SELECT id FROM test_table ORDER BY RAND() LIMIT 1) AS sub)"
            )
            preparedStatement.use {
                val name = "UpdatedUser${System.currentTimeMillis()}"
                val email = "updated_user${System.currentTimeMillis()}@example.com"
                val age = (18..80).random()
                
                it.setString(1, name)
                it.setString(2, email)
                it.setInt(3, age)
                it.executeUpdate()
            }
        }
    }

    override fun deleteData() {
        val connection = getConnection()
        connection.use {
            val preparedStatement = it.prepareStatement(
                "DELETE FROM test_table WHERE id = (SELECT id FROM (SELECT id FROM test_table ORDER BY RAND() LIMIT 1) AS sub)"
            )
            preparedStatement.use {
                it.executeUpdate()
            }
        }
    }
}